package queryx

import (
	"errors"
	"time"

	"github.com/jinzhu/now"
	"github.com/tidwall/gjson"
	"gorm.io/gorm"
	"gorm.io/gorm/clause"
)

func ParseToGormDB(db *gorm.DB, cond string) (*gorm.DB, error) {
	if !gjson.Valid(cond) {
		return nil, errors.New("invalid json")
	}
	res := gjson.Parse(cond)

	if !res.IsArray() {
		return nil, errors.New("invalid json array")
	}
	// ForEach方法无序，因此需要先行取出
	var hasVisited, visitedTrue bool
	fs := res.Get("#.field").Array()
	for _, v := range fs {
		if v.String() == "visited" {
			hasVisited = true
			r := res.Get("#(field==\"visited\").value")
			visitedTrue = r.Int() > 0
		}
	}

	res.ForEach(func(key, value gjson.Result) bool {
		field := value.Get("field")
		oper := value.Get("oper")
		val := value.Get("value")
		if !field.Exists() || !oper.Exists() || !val.Exists() {
			return true
		}

		dbField := field.String()
		if !inArray(oper.String(), supportOpers) || !inArray(dbField, supportFields) {
			return true
		}

		if (oper.String() == "in" || oper.String() == "range" || oper.String() == "nin") && !val.IsArray() {
			return true
		}

		if dbField == "device" {
			dbField = "last_device"
		}

		switch oper.String() {
		case "eq":
			if dbField == "age" {
				dbField = "year(birthday)"
				age := int(val.Int())
				db = db.Where("YEAR(birthday) = ?", now.With(time.Now().AddDate(-age, 0, 0)).Year())
			} else if dbField == "sex" {
				db = db.Where(dbField+" = ?", val.Int())
			} else if dbField == "visited" {
				hasVisited = true
				visitedTrue = val.Int() > 0
				whereK := "exists (select * from project_distribute_info where project_distribute_info.staff_id = staff_resource.id and project_distribute_info.visit_state = 2 )"
				if !visitedTrue {
					whereK = "not " + whereK
				}
				db = db.Where(whereK)
				return true
			} else {
				db = db.Where(dbField+" = ?", val.Value())
			}
		case "in":
			vls := val.Array()
			if len(vls) == 0 {
				return true
			}
			var vs []interface{}
			for _, v := range vls {
				if dbField == "tags" || dbField == "projects" || dbField == "sex" {
					vs = append(vs, v.Int())
				} else {
					vs = append(vs, v.Value())
				}

			}
			if dbField == "address" {
				tmpAddr := parseAddress(vs)
				var eqs []clause.Expression
				for k, v := range tmpAddr {
					eqs = append(eqs, clause.IN{Column: k, Values: v})
				}
				if len(eqs) == 1 {
					db = db.Where(eqs[0])
				} else {
					db = db.Where(clause.Or(eqs...))
				}
			} else if dbField == "tags" {
				db = db.Where("exists ( select * from taggables where taggables.object_type = ? and taggables.object_id in ?)", "App\\\\Models\\\\StaffResource", vs)
			} else if dbField == "projects" {
				whereK := "exists (select * from project_distribute_info where project_distribute_info.staff_id = staff_resource.id and project_distribute_info.visit_state = 2 and project_distribute_info.project_id in ?)"
				if hasVisited && !visitedTrue {
					whereK = "not " + whereK
				}
				db = db.Where(whereK, vs)
			} else {
				db = db.Where(dbField, vs)
			}
		case "nin":
			var vs []interface{}
			for _, v := range val.Array() {
				vs = append(vs, v.Value())
			}
			db = db.Not(dbField, vs)
		case "range":
			if !val.IsArray() || len(val.Array()) < 2 {
				return true
			}
			tmp := val.Array()
			if dbField == "age" {
				dbField = "year(birthday)"
				tmpAge1 := tmp[0].Int()
				tmpAge2 := tmp[1].Int()
				lesser := now.With(time.Now().AddDate(-int(tmpAge2), 0, 0)).Year()
				bigger := now.With(time.Now().AddDate(-int(tmpAge1), 0, 0)).Year()
				db = db.Where(dbField+" BETWEEN ? AND ?", lesser, bigger)
			} else {
				db = db.Where(dbField+" BETWEEN ? AND ?", tmp[0].Value(), tmp[1].Value())
			}

		}
		return true
	})
	return db, nil
}
